package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import br.com.ffm.praticas.bean.*;
import br.com.ffm.praticas.connection.ConnectionDB;

public class CityDAO {
	
	/**
	 * Method that returns all Cities
	 * @return List<City>
	 * @throws SQLException
	 */
	public List<City> getCities() throws SQLException {
		String query = "SELECT * FROM city ORDER BY description";
		List<City> list = new LinkedList<City>();
		StateDAO stateDAO = new StateDAO();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			City city = new City();
			city.setId(result.getInt("id"));
			city.setDescription(result.getString("description"));
			city.setState(stateDAO.getState(result.getInt("state_id")));
			list.add(city);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that returns a specific City for your id
	 * @param id City Identifier
	 * @return City
	 * @throws SQLException
	 */
	public City getCity(int id) throws SQLException {
		String query = "SELECT * FROM city WHERE (id = " + id + ")";
		City city;
		StateDAO stateDAO = new StateDAO();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			city = new City();
			city.setId(result.getInt("id"));
			city.setDescription(result.getString("description"));
			city.setState(stateDAO.getState(result.getInt("state_id")));
			ConnectionDB.getInstance().close();
			return city;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that returns a specific City for your description
	 * @param description City Description
	 * @return City
	 * @throws SQLException
	 */
	public City getCity(String desciption) throws SQLException {
		String query = "SELECT * FROM city WHERE (description = '" + desciption + "')";
		City city;
		StateDAO stateDAO = new StateDAO();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			city = new City();
			city.setId(result.getInt("id"));
			city.setDescription(result.getString("description"));
			city.setState(stateDAO.getState(result.getInt("state_id")));
			ConnectionDB.getInstance().close();
			return city;
		}
		ConnectionDB.getInstance().close();
		return null;
	}

}
